package com.DBUtil;

import java.awt.image.SampleModel;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import oracle.sql.CLOB;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.frameworkset.common.poolman.DBUtil;




public class DbHelper {


private Class beanClass = null;
private Connection conn = null;
private  boolean CANNOT_AUTO_COMMIT = true;
private final Log logger = LogFactory.getLog(DbHelper.class);

/**
* 根据数据库类型设置驱动程序
* @param dbms 数据库类型，如mysql、postgresql等
*/
private void fillDbmsDriver(String dbms) {

}

/**
* 根据数据库类型设置端口
* @param dbms 数据库类型
*/
private void fillDbmsPort(String dbms) {

}


/**
* 设置数据连接时需要的参数
* @param dbms 数据库类型
* @param host 主机
* @param db 数据库名
* @param user 用户名
* @param password 密码
 * @throws SQLException 
*/
public DbHelper()  {
 connect("oracle");
}

public DbHelper(String dbtype)  {
	 connect(dbtype);
	}


/**
* 加载驱动
 * @throws SQLException 
*/
public void connect(String dbtype) {
   if (conn != null) return;
   else{
	 // conn=SimpleConnetionPool.getConnection();
	   try {
		conn=DBUtil.getConection();
	} catch (SQLException e) {
		conn=null;
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   //使用c3p0的数据连接池。
	 /*  if(dbtype.equalsIgnoreCase("oracle"))
		   conn=ConnectionManager.getInstance().getConnection();
	   else//加载mysql驱动
		   conn=SimpleConnetionPool.getConnection();  */
	
  }
}
/**
 * 开启一个事务，将Connection的自动提交设为false
 */
public void beginTransaction(){
 try {
  CANNOT_AUTO_COMMIT=false;
  conn.setAutoCommit(CANNOT_AUTO_COMMIT);
 } catch (SQLException e) {
  System.err.println("Cannot set auto commit be false");
 }
}
/**
 * 设置是否启用事务处理
 * @param Cmt
 * @throws SQLException
 */
public void setAutoCommit(boolean Cmt) throws SQLException{
	if(conn!=null){
		conn.setAutoCommit(Cmt);
	}
}
/**
 * 提交事务，并关闭连接
 */
public void commit(){
	try {
		DbUtils.commitAndCloseQuietly(conn);
		conn=null;
	} catch (Exception e) {
		e.printStackTrace();
		// TODO: handle exception
	}
}
/**
 * 回滚事务，并关闭连接
 */
public void rollback(){
	try {
		DbUtils.rollbackAndCloseQuietly(conn);
		conn=null;
	} catch (Exception e) {
		e.printStackTrace();
		// TODO: handle exception
	}
}
/**
* 关闭Connection
*/
public void close() {
   if(!CANNOT_AUTO_COMMIT)return;
   try {
    DbUtils.close(conn);
    conn = null;
   } catch (SQLException e) {
    logger.error("Database close failed!");
    logger.error(e.getMessage());
   }
}


/**
* 根据无参sql语句进行查询，并返回一个对象，用于单条记录的查询
* @param sql 
* @return Object
*/
public Object query(String sql) {
   if (conn == null || beanClass == null)
    return null;

   QueryRunner run = new QueryRunner();
   ResultSetHandler h = new BeanHandler(beanClass);
   Object result = null;

   try {
    result = run.query(conn, sql, h);
   } catch (SQLException e) {
    logger.info("SQLException in [" + sql + "]");
    logger.error(e.getMessage());
   }

   return result;
}


/**
* 根据有参sql语句进行查询，并返回一个对象，用于单条记录的查询
* @param sql 
* @param args 
* @return Object
*/
public Object query(String sql, Object... args) {
   if (conn == null || beanClass == null)
    return null;

   QueryRunner run = new QueryRunner();
   ResultSetHandler h = new BeanHandler(beanClass);
   Object result = null;

   try {
    result = run.query(conn, sql, args, h);
   } catch (SQLException e) {
    logger.info("SQLException in [" + sql + "]");
    logger.error(e.getMessage());
   }

   return result;
}

/**
* 根据有参sql语句进行查询，并返回一个对象，用于单条记录的查询
* @param sql 
* @param args 
* @return Object
*/
@SuppressWarnings("deprecation")
public Map query4Map(String sql, Object... args) {
   if (conn == null)
    return null;
   RowProcessor rowprocessor = new BasicRowProcessor();

   QueryRunner run = new QueryRunner();
   ResultSetHandler h = new  MapHandler(rowprocessor);
   Map result = null;
 
   try {
    result = (Map) run.query(conn, sql,args, h);
   // System.out.println("SQL select:  [" + sql + "]");
   } catch (SQLException e) {
	   System.out.println("SQLException in [" + sql + "]");
	   System.out.println(e.getMessage());
   }

   return result;
}
/**
* 根据无参sql语句进行查询，并返回一个数据集，用于多条记录的查询
* @param sql
* @return ArrayList
*/
public ArrayList queryRecords(String sql,Class bean) {
   if (conn == null || bean == null)
    return null;

   QueryRunner run = new QueryRunner();
   ResultSetHandler h = new BeanListHandler(bean);
   ArrayList result = null;

   try {
    result = (ArrayList) run.query(conn, sql, h);
    // obtain the quaried records
   } catch (SQLException e) {
    logger.info("SQLException in [" + sql + "]");
    logger.error(e.getMessage());
   }

   return result;
}


/**
* 根据有参sql语句进行查询，并返回一个数据集，用于多条记录的查询
* @param sql
* @param args
* @return
*/
public ArrayList queryRecords(String sql, Object... args) {
   if (conn == null || beanClass == null)
    return null;

   QueryRunner run = new QueryRunner();
   ResultSetHandler h = new BeanListHandler(beanClass);
   ArrayList result = null;

   try {
    result = (ArrayList) run.query(conn, sql, args, h);
   
    System.out.println(result.size());
    // obtain the quaried records
   } catch (SQLException e) {
	   System.out.println("SQLException in [" + sql + "]");
	   System.out.println(e.getMessage());
   }

   return result;
}

public ArrayList queryRecords4Map(String sql, Object... args) throws Exception {
	   if (conn == null)
	    return null;

	   QueryRunner run = new QueryRunner();
	   ResultSetHandler h = new MapListHandler();
	   ArrayList result = null;

	   try {
	    result = (ArrayList) run.query(conn, sql, args, h);
	   
	    //System.out.println("SQL select:  [" + sql + "]");
	    // obtain the quaried records
	   } catch (SQLException e) {
	    System.out.println("SQLException in [" + sql + "]");
	    
	    System.out.println(e.getMessage());
	    throw new Exception("SQLException in [" + sql + "]"+e.getMessage());
	   }

	   return result;
	}
/**
* 根据无参sql语句进行数据更新，并返回更新后影响的记录数
* @param sql
* @return int
*/
public int update(String sql) {
   if (conn == null)
    return 0;

   QueryRunner run = new QueryRunner();
   int result = 0;
   //System.out.println("dbtool in [" + sql + "]");
   try {
    result = run.update(conn, sql);
   } catch (SQLException e) {
    System.out.println("SQLException in [" + sql + "]");
    System.out.println(e.getMessage());
   }

   return result;
}


/**
* 根据有参sql语句进行数据更新，并返回更新后影响的记录数
* @param sql
* @param args
* @return int
 * @throws SQLException 
*/
public int update(String sql, Object... args) throws SQLException {
   if (conn == null)
    return -1;
  
   QueryRunner run = new QueryRunner();
   int result = 0;
   //System.out.println("dbtool in [" + sql + "]");
   try {
    result = run.update(conn, sql, args);
   } catch (SQLException e) {
	   System.out.println("SQLException in [" + sql + "]");
	   System.out.println(e.getMessage());
	   throw new SQLException(e.getMessage());
   }

   return result;
}


/**
* 设置存放查询结果的Bean类，每个Bean类对象对应一条查询的结果记录
* @param beanClass，如User.class
*/
public void setBeanClass(Class beanClass) {
   this.beanClass = beanClass;
}


/**
* 设置使用外部的Connection对象，如来自数据库连接池的连接
* @param conn Connection对象
*/
public void setConn(Connection conn) {
   this.conn = conn;
}


@Override
protected void finalize() throws Throwable {
   // TODO Auto-generated method stub
   close();
   super.finalize();
}
public static void main(String[] args) throws Exception {
	   //Connection con = SimpleConnetionPool.getConnection(); 
	  String sql = "select to_char(menuseq)menuseq from z_rolerefmenu";


	   DbHelper dh = new DbHelper("oracle");

	   //u是从数据库查询出来的记录对象
	    Map u0=(Map)dh.query4Map(sql);
	   String s= u0.get("menuseq").toString();
	   System.out.print(s);
	   //u2是从数据库查询出来的记录对象
	   //User u2 = (User) dh.query("SELECT id,name,age,address FROM t_user WHERE id=?", n);
	  
	   // users和user2存放着从数据库查询出来的记录对象集合
	/*   List users = dh.queryRecords4Map("SELECT operatorid,username FROM ft_user");
	  // List users2 = dh.queryRecords("SELECT id,name,age,address FROM t_user WHERE id>?", n);
	  for(int i=0;i<users.size();i++){
		  Map m=(Map)users.get(i);
		  for(Object o:m.keySet()){
			  System.out.print(m.get(o)+"  ");
		  }
		  System.out.println("");
	  }*/
	  
	   //修改表记录
	  // dh.update("UPDATE ft_user SET username='abc2' WHERE operatorid=72");
	  
	   //根据提供的参数修改表记录
	   //dh.update("UPDATE t_user SET name='abc' WHERE id=?", n);
	  
	   dh.close();
	   //SimpleConnetionPool.printDebugMsg();
	   /*
	    * DbHelper tqr = new DbHelper();  
	      tqr.beginTransaction();
	      try {
	       tqr.update("insert into test values('a','b','c')");
	       System.out.println("执行了更新...");
	       tqr.update("insert into test values ('p', '16', 'com')");
	       System.out.println("执行插入...");
	      throw new SQLException("Throw SQLException");
	     } catch (Exception e) {
	      System.out.println("抛出了异常，准备回滚..."+e.getMessage());
	      tqr.rollback();
	    }
	       tqr.commit();
	    * 
	    */
	   
	}
}